from django.contrib.auth.models import User
from django.db.models import Avg, Count, Sum, F, Q
from django.utils import timezone
from datetime import timedelta, datetime
import json
from django.db import connection
from .models import Music, Rating, Comment, PlayHistory

def admin_stats(request):
    """
    为管理员仪表板提供数据统计
    """
    # 添加调试信息
    print("生成管理员统计数据...")
    
    # 只有超级用户或工作人员才能访问这些数据
    if not request.user.is_authenticated or not request.user.is_staff:
        print("用户未登录或不是管理员")
        return {
            'music_count': 0,
            'user_count': 0,
            'rating_count': 0,
            'comment_count': 0,
            'history_count': 0,
            'month_labels': '["1月", "2月", "3月", "4月", "5月", "6月"]',
            'rating_counts': '[0, 0, 0, 0, 0, 0]',
            'comment_counts': '[0, 0, 0, 0, 0, 0]',
            'genre_labels': '["流派1", "流派2", "流派3", "流派4", "流派5"]',
            'genre_counts': '[0, 0, 0, 0, 0]',
            'language_labels': '["语言1", "语言2", "语言3", "语言4", "语言5"]',
            'language_counts': '[0, 0, 0, 0, 0]'
        }
    
    print("开始收集统计数据...")
    
    # 基础统计 - 使用select_related和prefetch_related减少数据库查询
    try:
        # 使用原始SQL查询获取精确的统计数据
        with connection.cursor() as cursor:
            # 获取音乐总数
            cursor.execute("SELECT COUNT(*) FROM music_music")
            music_count = cursor.fetchone()[0]
            
            # 获取用户总数
            cursor.execute("SELECT COUNT(*) FROM auth_user")
            user_count = cursor.fetchone()[0]
            
            # 获取评分总数
            cursor.execute("SELECT COUNT(*) FROM music_rating")
            rating_count = cursor.fetchone()[0]
            
            # 获取评论总数
            cursor.execute("SELECT COUNT(*) FROM music_comment")
            comment_count = cursor.fetchone()[0]
            
            # 获取播放历史总数
            cursor.execute("SELECT COUNT(*) FROM music_playhistory")
            history_count = cursor.fetchone()[0]
            
        print(f"基础统计 (SQL): 音乐({music_count}), 用户({user_count}), 评分({rating_count}), 评论({comment_count})")
    except Exception as e:
        print(f"Error in basic stats SQL query: {e}")
        # 使用ORM作为备用方案
        music_count = Music.objects.count()
        user_count = User.objects.count()
        rating_count = Rating.objects.count()
        comment_count = Comment.objects.count()
        history_count = PlayHistory.objects.count()
        print(f"基础统计 (ORM): 音乐({music_count}), 用户({user_count}), 评分({rating_count}), 评论({comment_count})")
    
    # 获取过去6个月的评分和评论统计
    months_data = []
    month_labels = []
    rating_counts = []
    comment_counts = []
    
    try:
        now = timezone.now()
        # 修正：生成正确的六个月标签（不重复）
        correct_months = []
        for i in range(5, -1, -1):  # 从6个月前到当前
            # 安全计算月份，避免日期范围错误
            month_start = (now.replace(day=1) - timedelta(days=1)).replace(day=1)
            month_start = month_start.replace(month=(now.month - i) % 12 or 12,
                                           year=now.year - ((now.month - i) <= 0))
            correct_months.append(month_start)
            
        # 排序月份，确保按时间顺序
        correct_months.sort()
        print(f"排序后的月份: {[m.strftime('%Y-%m') for m in correct_months]}")
        
        # 依次处理每个月份
        for month_start in correct_months:
            # 计算下个月的开始日期
            if month_start.month == 12:
                next_month = month_start.replace(year=month_start.year + 1, month=1)
            else:
                next_month = month_start.replace(month=month_start.month + 1)
            
            # 获取月份标签 - 只显示月份数字
            month_name = str(month_start.month) + "月"
            month_labels.append(month_name)
            
            # 获取每月评分和评论数量 - 使用带索引的字段优化查询
            month_ratings = Rating.objects.filter(created_at__gte=month_start, created_at__lt=next_month).count()
            month_comments = Comment.objects.filter(created_at__gte=month_start, created_at__lt=next_month).count()
            
            rating_counts.append(month_ratings)
            comment_counts.append(month_comments)
            print(f"月份统计: {month_start.strftime('%Y-%m')} 评分:{month_ratings} 评论:{month_comments}")
    except Exception as e:
        print(f"Error in monthly stats: {e}")
        month_labels = ['1月', '2月', '3月', '4月', '5月', '6月']
        rating_counts = [0, 0, 0, 0, 0, 0]
        comment_counts = [0, 0, 0, 0, 0, 0]
    
    print(f"月度数据: {month_labels}")
    print(f"评分数据: {rating_counts}")
    print(f"评论数据: {comment_counts}")
    
    # 用户增长数据（最近4周）
    user_growth = []
    try:
        for i in range(4, 0, -1):
            start_date = now - timedelta(days=i*7)
            end_date = start_date + timedelta(days=7)
            count = User.objects.filter(date_joined__gte=start_date, date_joined__lt=end_date).count()
            week_label = f"周{i}"
            user_growth.append({"week": week_label, "count": count})
    except Exception as e:
        print(f"Error in user growth stats: {e}")
        user_growth = [{"week": "周4", "count": 0}, {"week": "周3", "count": 0}, 
                      {"week": "周2", "count": 0}, {"week": "周1", "count": 0}]
    
    # 评分最高的音乐 - 使用索引查询并限制返回字段
    top_rated_music = []
    try:
        # 使用原始SQL查询获取评分最高的音乐
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT id, song_name, artist_name, average_rating, rating_count 
                FROM music_music 
                WHERE rating_count > 0 
                ORDER BY average_rating DESC 
                LIMIT 5
            """)
            top_music_query = cursor.fetchall()
        
        for music in top_music_query:
            top_rated_music.append({
                "id": music[0],
                "song_name": music[1],
                "artist_name": music[2],
                "avg_rating": float(round(music[3], 1)),
                "num_ratings": music[4]
            })
        
        # 如果没有评分数据，添加一些热门音乐作为推荐
        if not top_rated_music:
            with connection.cursor() as cursor:
                cursor.execute("""
                    SELECT id, song_name, artist_name
                    FROM music_music
                    ORDER BY id DESC
                    LIMIT 5
                """)
                popular_music = cursor.fetchall()
                
            for music in popular_music:
                top_rated_music.append({
                    "id": music[0],
                    "song_name": music[1],
                    "artist_name": music[2],
                    "avg_rating": 0,
                    "num_ratings": 0
                })
    except Exception as e:
        print(f"Error in top rated music: {e}")
    
    print(f"评分最高的音乐: {len(top_rated_music)} 首")
    
    # 获取不同流派的音乐数量
    genre_stats = {}
    genre_labels = []
    genre_counts = []
    
    try:
        # 使用原始SQL查询优化性能，解决流派是逗号分隔的问题
        with connection.cursor() as cursor:
            # 先提取所有不同的流派字符串
            cursor.execute("""
                SELECT DISTINCT genre_ids FROM music_music 
                WHERE genre_ids IS NOT NULL AND genre_ids != ''
                LIMIT 1000
            """)
            all_genres = cursor.fetchall()
            
        print(f"获取到 {len(all_genres)} 条流派记录")
        
        # 处理流派数据，计算每个流派的音乐数量
        for genre_tuple in all_genres:
            genre_string = genre_tuple[0]
            if genre_string:  # 确保不是空字符串
                # 分割流派字符串（如果有多个流派用逗号分隔）
                for g in genre_string.split(','):
                    g = g.strip()
                    if g:
                        if g in genre_stats:
                            genre_stats[g] += 1
                        else:
                            genre_stats[g] = 1
        
        # 获取前5个流派
        genre_data = sorted(genre_stats.items(), key=lambda x: x[1], reverse=True)[:5]
        if genre_data:
            genre_labels = [item[0] if item[0] else "未知" for item in genre_data]
            genre_counts = [item[1] for item in genre_data]
        else:
            # 使用默认值
            genre_labels = ['流派1', '流派2', '流派3', '流派4', '流派5']
            genre_counts = [0, 0, 0, 0, 0]
            
        print(f"流派统计: {genre_labels} {genre_counts}")
    except Exception as e:
        print(f"Error in genre stats: {e}")
        genre_labels = ['流派1', '流派2', '流派3', '流派4', '流派5']
        genre_counts = [0, 0, 0, 0, 0]
    
    # 语言分布
    language_labels = []
    language_counts = []
    try:
        # 使用原始SQL查询统计每种语言的音乐数量
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT language, COUNT(*) as count 
                FROM music_music 
                WHERE language IS NOT NULL 
                GROUP BY language 
                ORDER BY count DESC 
                LIMIT 5
            """)
            language_stats = cursor.fetchall()
            
        if language_stats:
            language_labels = [item[0] if item[0] else "未知" for item in language_stats]
            language_counts = [item[1] for item in language_stats]
        else:
            # 使用默认值
            language_labels = ['语言1', '语言2', '语言3', '语言4', '语言5']
            language_counts = [0, 0, 0, 0, 0]
            
        print(f"语言统计: {language_labels} {language_counts}")
    except Exception as e:
        print(f"Error in language stats: {e}")
        language_labels = ['语言1', '语言2', '语言3', '语言4', '语言5']
        language_counts = [0, 0, 0, 0, 0]
    
    # 最近添加的音乐 - 只查询必要字段
    recent_music = []
    try:
        # 使用原始SQL查询获取最近添加的音乐
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT id, song_name, artist_name
                FROM music_music
                ORDER BY id DESC
                LIMIT 5
            """)
            latest_music = cursor.fetchall()
            
        for music in latest_music:
            recent_music.append({
                "id": music[0],
                "song_name": music[1],
                "artist_name": music[2],
                "upload_date": timezone.now().strftime('%Y-%m-%d')
            })
    except Exception as e:
        print(f"Error in recent music: {e}")
        
    print(f"最近添加的音乐: {len(recent_music)} 首")
    
    # 最近的评论 - 使用select_related减少查询
    recent_comments = []
    try:
        latest_comments = Comment.objects.select_related('user', 'music').order_by('-created_at')[:5]
        for comment in latest_comments:
            recent_comments.append({
                "id": comment.id,
                "user": comment.user.username,
                "music": comment.music.song_name,
                "text": comment.content[:50] + "..." if len(comment.content) > 50 else comment.content,
                "created_at": comment.created_at.strftime('%Y-%m-%d %H:%M')
            })
    except Exception as e:
        print(f"Error in recent comments: {e}")
    
    # 最活跃用户
    most_active_users = []
    try:
        active_users_query = User.objects.annotate(
            activity_count=Count('ratings', distinct=True) + 
                           Count('comments', distinct=True) + 
                           Count('play_histories', distinct=True)
        ).order_by('-activity_count')[:5]
        
        for user in active_users_query:
            most_active_users.append({
                "id": user.id,
                "username": user.username,
                "activity_count": user.activity_count
            })
    except Exception as e:
        print(f"Error in active users: {e}")
    
    # 手动JSON序列化 - 确保不会有任何编码问题
    try:
        # 确保数据的完整性
        if len(month_labels) != 6:
            print("警告: 月度标签数量不足6个，补充默认值")
            # 确保有6个月的数据
            default_months = ['1月', '2月', '3月', '4月', '5月', '6月']
            month_labels = month_labels[:6]  # 取前6个
            while len(month_labels) < 6:
                month_labels.append(default_months[len(month_labels)])
        
        if len(rating_counts) != 6:
            print("警告: 评分数据不足6个，补充默认值")
            rating_counts = rating_counts[:6]  # 取前6个
            while len(rating_counts) < 6:
                rating_counts.append(0)
                
        if len(comment_counts) != 6:
            print("警告: 评论数据不足6个，补充默认值")
            comment_counts = comment_counts[:6]  # 取前6个
            while len(comment_counts) < 6:
                comment_counts.append(0)
        
        # 确保至少有一些流派和语言数据
        if not genre_labels or len(genre_labels) < 5:
            print("警告: 流派数据不足，使用默认值")
            genre_labels = ['流派1', '流派2', '流派3', '流派4', '流派5']
            genre_counts = [0, 0, 0, 0, 0]
            
        if not language_labels or len(language_labels) < 5:
            print("警告: 语言数据不足，使用默认值")
            language_labels = ['语言1', '语言2', '语言3', '语言4', '语言5']
            language_counts = [0, 0, 0, 0, 0]
            
        # 序列化为JSON
        month_labels_json = json.dumps(month_labels, ensure_ascii=False)
        rating_counts_json = json.dumps(rating_counts)
        comment_counts_json = json.dumps(comment_counts)
        genre_labels_json = json.dumps(genre_labels, ensure_ascii=False)
        genre_counts_json = json.dumps(genre_counts)
        language_labels_json = json.dumps(language_labels, ensure_ascii=False)
        language_counts_json = json.dumps(language_counts)
        
        print(f"JSON化月度标签: {month_labels_json}")
        print(f"JSON化评分数据: {rating_counts_json}")
        print(f"JSON化流派标签: {genre_labels_json}")
        print(f"JSON化语言标签: {language_labels_json}")
    except Exception as e:
        print(f"JSON序列化错误: {e}")
        month_labels_json = '["1月", "2月", "3月", "4月", "5月", "6月"]'
        rating_counts_json = '[0, 0, 0, 0, 0, 0]'
        comment_counts_json = '[0, 0, 0, 0, 0, 0]'
        genre_labels_json = '["流派1", "流派2", "流派3", "流派4", "流派5"]'
        genre_counts_json = '[0, 0, 0, 0, 0]'
        language_labels_json = '["语言1", "语言2", "语言3", "语言4", "语言5"]'
        language_counts_json = '[0, 0, 0, 0, 0]'
    
    # 构建最终上下文
    context = {
        'music_count': music_count,
        'user_count': user_count,
        'rating_count': rating_count,
        'comment_count': comment_count,
        'history_count': history_count,
        'user_growth': user_growth,
        'top_rated_music': top_rated_music,
        'recent_music': recent_music,
        'recent_comments': recent_comments,
        'most_active_users': most_active_users,
        'month_labels': month_labels_json,
        'rating_counts': rating_counts_json,
        'comment_counts': comment_counts_json,
        'genre_labels': genre_labels_json,
        'genre_counts': genre_counts_json,
        'language_labels': language_labels_json,
        'language_counts': language_counts_json,
        'total_music': music_count,
        'total_users': user_count,
        'total_ratings': rating_count,
        'total_comments': comment_count
    }
    
    print("完成统计数据生成")
    return context 